What are the different conditions to filter records using LIKE operator?
What are the different conditions to filter records using LIKE operator?
519
21-Dec-2023
Updated on 03-Mar-2025
Khushi Singh
03-Mar-2025SQL uses the LIKE operator to view records based on text pattern matches in specific database fields. The LIKE operator helps find particular text parts within database data. We use % and _ as the primary wildcard patterns when working with the LIKE operator.
The % wildcard expression matches zero or more characters at any position in a string search criteria. The expression WHERE
column_nameLIKE 'apple%' finds all values that start with the combination apple. LIKE '%tree' searches for values that end with the term tree. The expression LIKE '%book%' returns all values that include the word book anywhere within them.The _ wildcard matches only one character in this context. The SQL statement WHERE
column_nameLIKE 'b_t' finds words starting or ending with 'b' and followed by exactly one character 't' including bat, bit, and bot but not boat because it contains multiple characters between 'b' and 't'. SQL Server handles advanced pattern-matching procedures beyond base capabilities. The square bracket notation in the LIKE clause matches letters from a specific character set, including WHEREcolumn_nameLIKE 'b[aeiou]t'which finds both "bat" and "bit" results. Using the syntax likec[^aeiou]tmatches only cat and cut strings while rejecting cit and cet matches because the brackets exclude specific letters and punctuation marks.Databases differ in their way of handling text-matching sensitivity. Both MySQL and SQL Server perform case-insensitive LIKE searches automatically yet PostgreSQL demands usage of the ILIKE operator for this function. The LIKE operator needs proper indexing to work well on databases with many records. When SQL users understand these conditions they can enhance database searches by filtering results according to defined patterns.